﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IRepository;
using System.Data;
using System.Data.SqlClient;
using DBHelper;
using Entities;
namespace Repository
{
    public class SqlUserRoleRepository : IUserRoleRepository
    {
        /// <summary>
        /// 判断用户与角色的直接关系是否存在
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="roleID">角色ID</param>
        /// <returns>true:关系存在；false:关系不存在</returns>
        public bool Exits(string userID, string roleID)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select count(*) from BaseUserRole where UserID=@UserID and RoleID=@RoleID");
            SqlParameter[] parameters = {
                                            new SqlParameter("@UserID", SqlDbType.VarChar, 36),
                                            new SqlParameter("@RoleID", SqlDbType.VarChar, 36) };
            parameters[0].Value = userID;
            parameters[1].Value = roleID;
            int i = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbQuery.ToString(), parameters));
            return i > 0 ? true : false;
        }

        /// <summary>
        /// 得到和某角色具有直接关系的所有用户
        /// </summary>
        /// <param name="roleID">角色ID</param>
        /// <returns>用户集合</returns>
        public IEnumerable<Entities.User> GetUsersByRoleID(string roleID)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select p2.* from BaseUserRole p1,BaseUser p2 where p1.UserID=p2.ID and p1.RoleID=@RoleID");
            SqlParameter[] parameters = { new SqlParameter("@RoleID", SqlDbType.VarChar, 36) };
            parameters[0].Value = roleID;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            List<User> listUsers = new List<User>();
            while (dr.Read())
            {
                listUsers.Add(SqlDataReaderToEntity.DataReaderToUser(dr));
            }
            dr.Close();
            return listUsers.AsEnumerable();

        }

        /// <summary>
        /// 得到与某用户具有直接关系的角色
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>角色集合</returns>
        public IEnumerable<Entities.Role> GetRolesByUserID(string userID)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select p2.* from BaseUserRole p1,BaseRole p2 where p1.RoleID=p2.ID and p1.UserID=@UserID");
            SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.VarChar, 36) };
            parameters[0].Value = userID;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            List<Role> listRoles = new List<Role>();
            while (dr.Read())
            {
                listRoles.Add(SqlDataReaderToEntity.DataReaderToRole(dr));
            }
            dr.Close();
            return listRoles.AsEnumerable();
        }

        /// <summary>
        /// 删除用户与角色的直接关系
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="roleID">角色ID</param>
        /// <returns>true:删除成功；false:删除失败</returns>
        public bool Delete(string userID, string roleID)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseUserRole where UserID=@UserID and RoleID=@RoleID");
            SqlParameter[] parameters = {
                                            new SqlParameter("@UserID", SqlDbType.VarChar, 36),
                                            new SqlParameter("@RoleID", SqlDbType.VarChar, 36) };
            parameters[0].Value = userID;
            parameters[1].Value = roleID;
            int i = Convert.ToInt32(SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), parameters));
            return i > 0 ;
        }

        /// <summary>
        /// 删除用户角色关系（一个用户多个角色一起删除）
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="listRoleID">角色ID集合</param>
        /// <returns>true:删除成功；false:删除失败</returns>
        public bool DeleteByUserAndRoles(string userID, List<string> listRoleID)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseUserRole where UserID=@UserID and RoleID in(");
            for (int i = 0; i < listRoleID.Count; i++)
            {
                if (i == 0)
                {
                    sbDelete.Append("'" + listRoleID[i] + "'");
                }
                else
                {
                    sbDelete.Append(",'" + listRoleID[i] + "'");
                }
            }
            sbDelete.Append(")");
            SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.VarChar, 36) };
            parameters[0].Value = userID;
            int j = Convert.ToInt32(SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), parameters));
            return j > 0;
        }

        /// <summary>
        /// 删除用户角色关系（一个角色多个用户一起删除）
        /// </summary>
        /// <param name="roleID">角色ID</param>
        /// <param name="listUserID">用户ID集合</param>
        /// <returns>true:删除成功；false:删除失败</returns>
        public bool DeleteByRoleAndUsers(string roleID, List<string> listUserID)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseUserRole where RoleID=@RoleID and UserID in(");
            for (int i = 0; i < listUserID.Count; i++)
            {
                if (i == 0)
                {
                    sbDelete.Append("'" + listUserID[i] + "'");
                }
                else
                {
                    sbDelete.Append(",'" + listUserID[i] + "'");
                }
            }
            sbDelete.Append(")");
            SqlParameter[] parameters = { new SqlParameter("@RoleID", SqlDbType.VarChar, 36) };
            parameters[0].Value = roleID;
            int j = Convert.ToInt32(SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), parameters));
            return j > 0;
        }

        /// <summary>
        /// 添加用户与角色的直接关系
        /// </summary>
        /// <param name="userRole">用户角色关系</param>
        /// <returns>true:添加成功；false:添加失败</returns>
        public bool Add(Entities.UserRole userRole)
        {
            StringBuilder sbInsert = new StringBuilder();
            sbInsert.Append("insert into BaseUserRole(UserID,RoleID,Creator,CreateTime,Updator,UpdateTime)");
            sbInsert.Append("values (@UserID,@RoleID,@Creator,@CreateTime,@Updator,@UpdateTime)");
            SqlParameter[] parameters = {
                new SqlParameter("@UserID", SqlDbType.VarChar,36),
                new SqlParameter("@RoleID", SqlDbType.VarChar,36),
                new SqlParameter("@Creator", SqlDbType.NVarChar,50),
                new SqlParameter("@CreateTime", SqlDbType.DateTime),
                new SqlParameter("@Updator", SqlDbType.NVarChar,50),
                new SqlParameter("@UpdateTime", SqlDbType.DateTime)};
            parameters[0].Value = userRole.UserID;
            parameters[1].Value = userRole.RoleID;
            parameters[2].Value = userRole.Creator;
            parameters[3].Value = userRole.CreateTime;
            parameters[4].Value = userRole.Updator;
            parameters[5].Value = userRole.UpdateTime;
            int i = Convert.ToInt32(SqlHelper.ExecuteNonQuery(CommandType.Text, sbInsert.ToString(), parameters));
            return i > 0 ? true : false;
        }

        /// <summary>
        ///得到与某角色没有直接关系的所有用户 
        /// </summary>
        /// <param name="roleID">角色ID</param>
        /// <returns>用户集合</returns>
        public IEnumerable<User> GetUsersNotInRole(string roleID)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select * from BaseUser where  ID not in(select UserID from BaseUserRole where RoleID=@ID)");
            SqlParameter[] parameters = {
                new SqlParameter("@ID", SqlDbType.VarChar,36)};
            parameters[0].Value = roleID;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            List<User> listUser = new List<User>();
            while (dr.Read())
            {
                listUser.Add(SqlDataReaderToEntity.DataReaderToUser(dr));
            }
            dr.Close();
            return listUser.AsEnumerable();
        }

        /// <summary>
        /// 得到与某用户没有直接关系的所有角色
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>角色集合</returns>
        public IEnumerable<Role> GetRolesNotInUser(string userID)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select * from BaseRole where  ID not in(select RoleID from BaseUserRole where UserID=@ID)");
            SqlParameter[] parameters = {
                new SqlParameter("@ID", SqlDbType.VarChar,36)};
            parameters[0].Value = userID;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            List<Role> listRole = new List<Role>();
            while (dr.Read())
            {
                listRole.Add(SqlDataReaderToEntity.DataReaderToRole(dr));
            }
            dr.Close();
            return listRole.AsEnumerable();
        }



       
    }
}
